﻿    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Security;
    using webtracuudiem.Truyxuatdulieu;
    using System.Web.Security;
    using System.Data.OleDb;
    using System.Data.Common;
    using System.Data.SqlClient;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using System.IO;
//using Microsoft.Office.Interop.Excel; 

namespace webtracuudiem
{
    public partial class Nhapdiem : System.Web.UI.Page
    {
        public bool Editting = false;
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        private DataTable GetData(SqlCommand cmd)
        {
            DataTable dt = new DataTable();
//            String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings[@"Data Source=.\sqlexpress;Initial Catalog=demo1;Integrated Security=True"].ConnectionString;
            String strConnString = @"Data Source=.\;Initial Catalog=demo1;Integrated Security=True";
            SqlConnection con = new SqlConnection(strConnString);
            SqlDataAdapter sda = new SqlDataAdapter();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            try
            {
                con.Open();
                sda.SelectCommand = cmd;
                sda.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                sda.Dispose();
                con.Dispose();
            }
        }
        protected void ButNhapdiem_Click(object sender, EventArgs e)
        {

            Editting = true;
            btnLuu.Visible = true;
            GridView1.DataBind();
            //Response.Redirect(Request.RawUrl);
        }

        protected void ButXemĐiem_Click(object sender, EventArgs e)
        {
            Editting = false;
            GridView1.Visible = true;
            btnLuu.Visible = false;
            //Response.Redirect(Request.RawUrl);
        }

        protected void DropChonnmon_SelectedIndexChanged(object sender, EventArgs e)
        {
            Editting = false;
            btnLuu.Visible = false;
            //GridView1.DataBind();            
        }

        protected void nhapdiemSource_Selected(object sender, SqlDataSourceStatusEventArgs e)
        {
            string debug = "";
            foreach (System.Data.Common.DbParameter p in e.Command.Parameters)
            {
                debug += string.Format("{0}: {1}", p.ParameterName, p.Value);
            }
            Response.Write("<b>" + debug + "</b><br/>");
        }

        protected void btnLuu_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                GridViewRow row = GridView1.Rows[i];
                if (row.RowType != DataControlRowType.DataRow)
                    continue;
                HiddenField hidenMaSV = (HiddenField)row.FindControl("hidenMaSV");
                string masv = hidenMaSV.Value;
                TextBox txtLan1 = (TextBox)row.FindControl("txtDiemL1");
                object diemLan1;
                if (string.IsNullOrEmpty(txtLan1.Text))
                    diemLan1 = null;
                else
                    diemLan1 = float.Parse(txtLan1.Text);
                TextBox txtLan2 = (TextBox)row.FindControl("txtDiemL2");
                object diemLan2;
                if (string.IsNullOrEmpty(txtLan2.Text))
                    diemLan2 = null;
                else
                    diemLan2 = float.Parse(txtLan2.Text);
                string maMH = DropChonnmon.SelectedValue;
                string sqlSelect = "SELECT * FROM Diem WHERE MaSV=@MaSV AND MaMH=@MaMH";
                DataTable tbl = TienIchSql.ChayLenhSelectDuLieu(sqlSelect, "@MaMH", maMH, "@MaSV", masv);
                string sql;
                if (tbl.Rows.Count == 0)
                    sql = "INSERT INTO Diem(MaSV,MaMH,DiemL1,DiemL2) VALUES(@MaSV,@MaMH,@DiemL1,@DiemL2)";
                else
                    sql = "UPDATE Diem SET DiemL1=@DiemL1,DiemL2=@DiemL2 WHERE MaSV=@MaSV AND MaMH=@MaMH";
                string[] mangTenThamSo = new string[] { "@MaSV", "@MaMH", "@DiemL1", "@DiemL2" };
                object[] mangGiaTriThamSo = new object[] { masv, maMH, diemLan1, diemLan2 };
                SqlDbType[] mangKieuThamSo = new SqlDbType[] { SqlDbType.Char, SqlDbType.Char, SqlDbType.Float, SqlDbType.Float };
                TienIchSql.ChayLenhCapNhatDuLieu(sql, mangTenThamSo, mangGiaTriThamSo, mangKieuThamSo);

            }
            GridView1.DataBind();
        }

        protected void ButThoat_Click(object sender, EventArgs e)
        {
            FormsAuthentication.SignOut();
            Response.Redirect("/Default.aspx");
        }
        protected void ExportToExcel(object sender, EventArgs e)
        {
            //Get the data from database into datatable
            string strQuery = "select * from diem";
            SqlCommand cmd = new SqlCommand(strQuery);
            DataTable dt = GetData(cmd);

            //Create a dummy GridView
            GridView GridView1 = new GridView();
            GridView1.AllowPaging = false;
            GridView1.DataSource = dt;
            GridView1.DataBind();

            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition",
             "attachment;filename=KetXuatFile.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);

            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                //Apply text style to each Row
                GridView1.Rows[i].Attributes.Add("class", "textmode");
            }
            GridView1.RenderControl(hw);

            //style to format numbers to string
            string style = @"<style> .textmode { mso-number-format:\@; } </style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }
        protected void btnAddfilexcel_Click(object sender, EventArgs e)
        {
            /*{
                try
                {
                    FileStream stream = File.Open(@"E:\Projects\Sheet1.xls", FileMode.Open, FileAccess.Read);
                    IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                    DataSet result = excelReader.AsDataSet();
                    excelReader.Close();
                    GridView1.DataSource = result.Tables[0];

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString(), "Error in Import");
                    return;
                }
                finally
                {
                    GFun.OleDbCon.Close();
                }
            } */
            //string PathConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + "C:/khoa.xls" + ";Extended Properties = \"Excel 8.0;HDR=Yes;\";";
            //OleDbConnection conn = new OleDbConnection(PathConn);
            //OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("select * from [Sheet1$]", conn);
            //DataTable dt = new DataTable();
            //myDataAdapter.Fill(dt);
            //GridView1.DataSource = dt;
            /*string strQuery = "select * from diem";
            SqlCommand cmd = new SqlCommand(strQuery);
            DataTable dt = GetData(cmd);

            //Create a dummy GridView
            GridView GridView1 = new GridView();
            GridView1.AllowPaging = false;
            GridView1.DataSource = dt;
            GridView1.DataBind();

            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition",
             "attachment;filename=GridViewExport.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);

            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                //Apply text style to each Row
                GridView1.Rows[i].Attributes.Add("class", "textmode");
            }
            GridView1.RenderControl(hw);
            
            //style to format numbers to string
            string style = @"<style> .textmode { mso-number-format:\@; } </style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
            */
            /*
            if (FileUpload1.PostedFile.ContentType == "application/vnd.ms-excel" ||
               FileUpload1.PostedFile.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            {
                try
                {
                    string filename = Path.Combine(Server.MapPath("~/ImportExcel"), Guid.NewGuid().ToString() + Path.GetExtension(FileUpload1.PostedFile.FileName));
                    FileUpload1.PostedFile.SaveAs(filename);
                    string conString = "";
                    string ext = Path.GetExtension(FileUpload1.PostedFile.FileName);
                    if (ext.ToLower() == ".xls")
                    {
                        conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + filename + ";Extended Properties = \"Excel 8.0;HDR=Yes;\";";
                    }
                    else if (ext.ToLower() == ".xlsx")
                    {
                        conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" + filename + ";Extended Properties = \"Excel 12.0;HDR=Yes;IMEX=2\"";
                    }
                    string query = "select * from khoa";
                    OleDbConnection con = new OleDbConnection(conString);
                    if (con.State == System.Data.ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    OleDbCommand cmd = new OleDbCommand(query, con);
                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    da.Dispose();
                    con.Close(); 
                    con.Dispose();
                    using (DEMO1Entities dc = new DEMO1Entities())
                    {
                        foreach (DataRow dr in ds.Tables[0].Rows)
                        {
                            string makhoa = dr["makhoa"].ToString();
                            var v = dc.Khoa.Where(a => a.Equals(makhoa)).FirstOrDefault();
                        }

                    }
                }
                catch (Exception)
                {
                    throw;
                }
            }*/
            /*string excelContentType = "application/vnd.ms-excel";
            string excel2010 = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            if (!FileUpload1.HasFile)
            {
                lblthongbao.Text = "Chọn file excel cần import!";
            }
            else
            {
                try
                {
                    if (FileUpload1.PostedFile.ContentType != excelContentType && FileUpload1.PostedFile.ContentType != excel2010)
                    { lblthongbao.Text = "Vui lòng chọn file excel!!"; }
                    else
                    {
                        string path = string.Concat(Server.MapPath("~/ImportExcel" + FileUpload1.FileName));
                        FileUpload1.SaveAs(path);

                        //sau khi upload xong doc du lieu tu file nay!
                        string excelConnetionstring = string.Format("Provider=Microsoft.JET.4.0;Data Source ={0};Extended Properties = Excel 8.0", path);
                        OleDbConnection connection = new OleDbConnection();
                        connection.ConnectionString = excelConnetionstring;
                        OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                        connection.Open();
                        OleDbDataReader dr = command.ExecuteReader();
                        //sau khi lay du lieu => insert vao sql sqlrver
                        string sqlConnectionstring = @"Data Source=.\sqlexpress;Initial Catalog=demo1;Integrated Security=True";
                        SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionstring);
                        bulkCopy.DestinationTableName = "Khoa2";//ten bang duoc chen
                        bulkCopy.WriteToServer(dr);
                        lblthongbao.Text = "Import thành công";
                        //HyperLink1.Visible = true;

                    }

                }
                catch { lblthongbao.Text = "Ðã có lỗi xảy ra trong quá trình import!!"; }
            }*/
            
                        string ExcelContentType = "application/vnd.ms-excel";
                        string Excel2010ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                        if (FileUpload1.HasFile)
                        {
                            //Check the Content Type of the file 
                            if (FileUpload1.PostedFile.ContentType == ExcelContentType || FileUpload1.PostedFile.ContentType == Excel2010ContentType)
                            {
                                try
                                {
                                    //Save file path 
                                    string path = string.Concat(Server.MapPath("~/"), FileUpload1.FileName);
                                    //Save File as Temp then you can delete it if you want 
                                    FileUpload1.SaveAs(path);
                                    //string path = @"C:\Users\Johnney\Desktop\ExcelData.xls"; 
                                    //For Office Excel 2010  please take a look to the followng link  
                                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);

                                    // Create Connection to Excel Workbook 
                                    using (OleDbConnection connection =
                                                 new OleDbConnection(excelConnectionString))
                                    {
                                        OleDbCommand command = new OleDbCommand
                                                ("Select * FROM [Sheet1$]", connection);

                                        connection.Open();

                                        // Create DbDataReader to Data Worksheet 
                                        using (DbDataReader dr = command.ExecuteReader())
                                        {

                                            // SQL Server Connection String 
                                            string sqlConnectionString = @"Data Source=.\;Initial Catalog=demo1;Integrated Security=True";

                                            // Bulk Copy to SQL Server 
                                            using (SqlBulkCopy bulkCopy =
                                                       new SqlBulkCopy(sqlConnectionString))
                                            {
                                                bulkCopy.DestinationTableName = "diem";
                                                bulkCopy.WriteToServer(dr);
                                                lblthongbao.Text = "Import thành công";
                                            }
                                        }
                                    }
                                }

                                catch (Exception ex)
                                {
                                    lblthongbao.Text = ex.Message;
                                }
                                Response.Redirect(Request.RawUrl);
            }
                        } 
        }

        protected void btnExportExcel_Click(object sender, EventArgs e)
        {
            //Get the data from database into datatable
            string strQuery = "select * from diem";
            SqlCommand cmd = new SqlCommand(strQuery);
            DataTable dt = GetData(cmd);

            //Create a dummy GridView
            GridView GridView1 = new GridView();
            GridView1.AllowPaging = false;
            GridView1.DataSource = dt;
            GridView1.DataBind();

            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition",
             "attachment;filename=BangDiemTongHop.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);

            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                //Apply text style to each Row
                GridView1.Rows[i].Attributes.Add("class", "textmode");
            }
            GridView1.RenderControl(hw);

            //style to format numbers to string
            string style = @"<style> .textmode { mso-number-format:\@; } </style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }

    }
    }